package com.sql;

import java.util.ArrayList;
import java.util.List;

import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.replace.Replace;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.util.TablesNamesFinder;

public class SqlParser {
	
	public static List<String> getTableNames(String sql)throws Exception{
		String sqlTemp = sql.trim().toUpperCase();
		SqlType type =null;
		if(sqlTemp.startsWith("SELECT")){
			type = SqlType.K_SELECT;
		}else if(sqlTemp.startsWith("UPDATE")){
			type = SqlType.K_UPDATE;
			sqlTemp= delLimit(sqlTemp);
		}else if(sqlTemp.startsWith("DELETE")){
			type = SqlType.K_DELETE;
			sqlTemp= delLimit(sqlTemp);
		}else if(sqlTemp.startsWith("INSERT")){
			type = SqlType.K_INSERT;
			sqlTemp= delLimit(sqlTemp);
		}else if(sqlTemp.startsWith("REPLACE")){
			type = SqlType.K_REPLACE;
			sqlTemp= delLimit(sqlTemp);
		}else if(sqlTemp.startsWith("CREATE")){
			type = SqlType.K_CREATE;
		}else if(sqlTemp.startsWith("CALL")){
			type = SqlType.K_PRC_CALL;
		}
		return parserSql(type,sqlTemp);
	}
	public static List<String> parserSql(SqlType type,String sql)throws Exception{
		List<String> list = new ArrayList<String>();
		Statement statement = null;
		if(type!= SqlType.K_PRC_CALL){
			statement = CCJSqlParserUtil.parse(sql);
		}
		TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
		switch(type){
		case K_SELECT:
			Select select = (Select)statement;
			list = tablesNamesFinder.getTableList(select);
			if(list.size()==0){// 处理只查询函数的情况，如select now()
				String sDbObjectName = sql.substring(6,sql.indexOf("(")).trim();
				list.add(sDbObjectName);
			}
			break;
		case K_UPDATE:
			Update update = (Update)statement;
			list = tablesNamesFinder.getTableList(update);
			break;		
		case K_DELETE:
			Delete delete = (Delete)statement;
			list = tablesNamesFinder.getTableList(delete);
			break;
		case K_INSERT:
			Insert insert = (Insert)statement;
			list = tablesNamesFinder.getTableList(insert);
			break;
		case K_REPLACE:
			Replace replace = (Replace)statement;
			list = tablesNamesFinder.getTableList(replace);
			break;
		case K_CREATE:
			CreateTable createTable = (CreateTable)statement;
			list = tablesNamesFinder.getTableList(createTable);
			break;
		case K_PRC_CALL:
			String sDbObjectName = sql.substring(4,sql.indexOf("(")).trim();
			list.add(sDbObjectName);
			break;
		default:
			throw new Exception("不支持的类型");
		}
		return list;
	} 
	/**
	 * 对delete update insert SQL语句中的limit 做特殊处理
	 * @param sql
	 */
	public static String delLimit(String sql) throws Exception{
		int orderOffSet = sql.lastIndexOf("ORDER");		
		char leftChar;
		char rightChar=0;
		if(orderOffSet>0){
			leftChar = sql.charAt(orderOffSet-1);
			rightChar = sql.charAt(orderOffSet+5);
			if((leftChar=='\n'||leftChar==' '||leftChar=='\r')&&
			  ((rightChar=='\n'||rightChar==' '||rightChar=='\r'))){
				//System.out.println(sql.substring(0,orderOffSet));
				return sql.substring(0,orderOffSet);
			}
		}
		int limitOffSet = sql.lastIndexOf("LIMIT");
		if(limitOffSet>0){
			leftChar = sql.charAt(limitOffSet-1);
			rightChar = sql.charAt(limitOffSet+5);
			if((leftChar=='\n'||leftChar==' '||leftChar=='\r')&&
			  ((rightChar=='\n'||rightChar==' '||rightChar=='\r'))){
				//System.out.println(sql.substring(0,limitOffSet));
				return sql.substring(0,limitOffSet);
			}
		}
		return sql;
	}
	public static void main(String[] args) throws Exception{
		String sql = "select * from T1 left join T2 on (t1.a=t2.b) where t1.a='a' group by name";
//		String sql = "select * from ECC_STAFF_MANAGER G where G.DEPT_ID in (select t.dept_id from ecc_dept_manager t limit 0,1)";
		List<String> list = SqlParser.getTableNames(sql);
		for(int i=0;i<list.size();i++){
			System.out.println("table"+String.valueOf(i)+":"+list.get(i));
		}

	}
}
